Re: LISTEN / NOTIFY performance in 8.3

At 1:13 PM -0500 2/25/08, Tom Lane wrote:
>Joel Stevenson <joelstevenson@mac.com> writes:
>>>  Also, it might be worth enabling log_lock_waits to see if the slow
>>>  notifies are due to having to wait on some lock or other.
>
>>  Turning on log_lock_waits shows that there is a lot of waiting for
>>  locks on the pg_listener table ala:
>
>Interesting.  The LISTEN/NOTIFY mechanism itself takes ExclusiveLock
>on pg_listener, but never for very long at a time (assuming pg_listener
>doesn't get horribly bloated, which we know isn't happening for you).
>
>Another thought that comes to mind is that maybe the delays you see
>come from these lock acquisitions getting blocked behind autovacuums of
>pg_listener.  I did not see that while trying to replicate your problem,
>but maybe the issue requires more update load on pg_listener than the
>test script can create by itself, or maybe some nondefault autovacuum
>setting is needed --- what are you using?

Default autovacuum settings.

I turned on all autovacuum logging and cranked up the test script and
have it fork 25 consumers each running 25 iterations.  At that level
on my machine I can get the lock waiting to exceed the 1s
deadlock_timeout right away but the autovacuum activity on
pg_listener is entirely absent until the end when the forked
consumers are mostly done and disconnected.